package webservice;

import interfaces.IWebService;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;

public class WebService implements IWebService {
	
	Database db;
	ResultSet rs;
	
	public static Logger logger = Logger.getLogger(MockupWebServiceClient.class);
	
	
	public WebService() {
		try {
			db = new Database(DBconfig.url, DBconfig.USER, DBconfig.PASS);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		rs = null;
	}
	
	@Override
	public ArrayList<String> getAvailableSellers(String service) {
		logger.info("Retrieving available sellers...");
		
		ArrayList<String> result = new ArrayList<String>();
		
		int service_id = getServiceId(service);
		
		String query = "SELECT c.`username` seller , a.`price` " +
						"FROM `user_services` a, `users` c "	+
						"WHERE a.`user_id` = c.`id` " +
					    "AND c.`user_type` = 2 " +
					    "AND c.`active` = 1 " +
					    "AND a.`service_id` = '" + service_id +"'";

		try 
		{			
			db.setCommand(query);
			rs = db.executeQuery();
			
			while(rs.next())
				result.add(rs.getString(1)+";"+rs.getInt(2));
		}
		
		catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}


	@Override
	public ArrayList<String> refreshSellers(String service) {
		logger.info("Retrieving available sellers...");
		
		ArrayList<String> result = new ArrayList<String>();
		
		int service_id = getServiceId(service);
		
		String query = "SELECT c.`username` seller , a.`price`, d.`name` " +
						"FROM `user_services` a, `auctions` b, `users` c, `status` d  "	+
						"WHERE a.`user_id` = c.`id` " +
					    "AND a.`user_id` = b.`seller_id` " +
					    "AND a.`service_id` = b.`service_id` " +
					    "AND b.`status_id` = d.`id` " +						
					    "AND c.`user_type` = 2 " +
					    "AND c.`active` = 1 " +
					    "AND a.`service_id` = '" + service_id +"'";

		try 
		{			
			db.setCommand(query);
			rs = db.executeQuery();
			
			while(rs.next())
				result.add(rs.getString(1)+";"+rs.getInt(2)+";"+rs.getString(3));
		}
		
		catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	
	//@Override
	public ArrayList<String> getAvailableBuyers(String service , String seller) {
		logger.info("Retrieving available buyers...");
		
		ArrayList<String> result = new ArrayList<String>();
		
		int service_id = getServiceId(service);
		int seller_id = getUserId(seller);
		
		String query = "SELECT c.`username` buyer , a.`price` , d.`name` " +
						"FROM `user_services` a,`auctions` b, `users` c, `status` d " +
						"WHERE a.`user_id` = c.`id` " +
					    "AND a.`user_id` = b.`buyer_id` " +
					    "AND a.`service_id` = b.`service_id` " +
					    "AND b.`status_id` = d.`id` " +
					    "AND b.`seller_id` = '" + seller_id + "'" +
					    "AND c.`user_type` = 1 " +
				     	"AND c.`active` = 1 " +
				     	"AND a.`service_id` = '" + service_id +"'";
		
		
		try 
		{			
			db.setCommand(query);
			rs = db.executeQuery();
			
			while(rs.next())
				result.add(rs.getString(1)+";"+rs.getInt(2)+";"+rs.getString(3));
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 

		return result;
	}
	
	
	public void launchOffer (String user, String servName) {
		logger.info(user + " launch offer for " + servName);
		
		ArrayList<String> a = new ArrayList<>();
		a.add(servName +";-1");
		
		addServices(user, a);
	}
	

	public void dropOffer (String user, String servName) {
		logger.info(user + " drop offer for " + servName);
		
		int user_id = getUserId(user);
		int service_id = getServiceId(servName);
		
		// Update `user_service` table:
		String query = "DELETE FROM `user_services` " +
						"WHERE `user_id` = '"+user_id+"' " +
						"AND `service_id` = '"+service_id+"' ";
		
		customExecute(query); 
		
		// Update `auctions` table:
		String query2 = "DELETE FROM `auctions` " +
						"WHERE `buyer_id` = '"+user_id+"' " +
						"AND `service_id` = '"+service_id+"'";
		
		customExecute(query2); 
	}


	public void refuseOffer(String user, String servName, String seller) {
		logger.info(user + " refused offer for " + servName + " from " + seller);
		
		int seller_id = getUserId(seller);
		int buyer_id = getUserId(user);
		int service_id = getServiceId(servName);
		
		String query ="UPDATE  `proiect_idp`.`auctions` SET  `status_id` =  '6' " +
						"WHERE  `auctions`.`buyer_id` ='" + buyer_id +"' " +
						"AND  `auctions`.`seller_id` ='" + seller_id +"' " +
						"AND  `auctions`.`service_id` ='" + service_id +"'";
		
		customExecute(query);
	}
	
	
	public void acceptOffer(String user, String servName, String seller) {
		logger.info(user + " accepted offer for " + servName + " from " + seller);
		
		int seller_id = getUserId(seller);
		int buyer_id = getUserId(user);
		int service_id = getServiceId(servName);
		
		
		String query ="UPDATE  `proiect_idp`.`auctions` SET  `status_id` =  '5' " +
						"WHERE  `auctions`.`buyer_id` ='" + buyer_id +"' " +
						"AND  `auctions`.`seller_id` ='" + seller_id +"' " +
						"AND  `auctions`.`service_id` ='" + service_id +"'";
		
		customExecute(query);
	}

	public void makeOffer(String username, String service, String buyer) {
		logger.info(username + " made an offer for " + service + " to " + buyer);
		
		int seller_id = getUserId(username);
		int buyer_id = getUserId(buyer);
		int service_id = getServiceId(service);
		
		
		String query ="UPDATE  `proiect_idp`.`auctions` SET  `status_id` =  '4' " +
						"WHERE  `auctions`.`buyer_id` ='" + buyer_id +"' " +
						"AND  `auctions`.`seller_id` ='" + seller_id +"' " +
						"AND  `auctions`.`service_id` ='" + service_id +"'";
		
		customExecute(query);
	}

	public void dropAuction(String username, String service, String buyer) {
		logger.info(username + " dropped an auction for " + service + " to " + buyer);
		
		int seller_id = getUserId(username);
		int buyer_id = getUserId(buyer);
		int service_id = getServiceId(service);
		
		
		String query ="UPDATE  `proiect_idp`.`auctions` SET  `status_id` =  '3' " +
					"WHERE  `auctions`.`buyer_id` ='" + buyer_id +"' " +
					"AND  `auctions`.`seller_id` ='" + seller_id +"' " +
					"AND  `auctions`.`service_id` ='" + service_id +"'";
		
		customExecute(query);
	}
	
	/**
	 * 
	 * @param username
	 * @param pass
	 * @return 	0 - login failed
	 * 			> 0 - user type ( 1 -buyer ; 2 -seller)
	 */
	public int login(String username , String pass){
		
		int login = 0;
		try 
		{
			String query = "SELECT user_type FROM users WHERE `username` = '" + username
						+"' AND `password` = '" + pass + "'";
			
			
			rs = customQuery(query);
			
			// Get the row count:
			int rowCount = rs.last() ? rs.getRow() : 0;
			
			if (rowCount > 0)
			{
				login = rs.getInt(1);
			
				// Set active state for user:
				String query_up		= "UPDATE  users SET  `active` =  '1' WHERE  `username` = '"+ username+"'";
		
				customExecute(query_up);
			}
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		return login;
	}
	
	public void addServices (String username , ArrayList<String> services){
		logger.info("User <" + username + "> is posting services.");
		
		try 
		{
			int user_id = getUserId(username);
			
			for (String s : services) {
				
				String[] split = s.split(";");
				String service_name = split[0];
				String price = split[1];
				
				String query	= "SELECT `id` FROM services" +
									" WHERE  `name` = '"+ service_name+"'";
				
				rs = customQuery(query);
				
				if (rs.next()){
					int service_id = rs.getInt(1);
					
					if (service_id > 0){
						String query_ins ="INSERT INTO  `user_services` ( `user_id` , `service_id` , " +
								"`price` ) " +
								"VALUES (" +
								user_id + " , " +
								service_id + " , " +
								price +
								")";
						
						customExecute(query_ins);
					}
				}
			}
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 		
	}
	
	public void addAuction (String user, String servName, String seller) {
		
		int seller_id = getUserId(seller);
		int buyer_id = getUserId(user);
		int service_id = getServiceId(servName);
		
		String query_ins ="INSERT INTO  `auctions` ( `buyer_id` ,`seller_id` ," +
				"`service_id` ,`status_id`)" +
				"VALUES (" +
				buyer_id + " , " +
				seller_id + " , " +
				service_id + " , " +
				" 3 )";
		
		customExecute(query_ins);
	}
	
	private int getUserId(String username) {

		int user_id = -1;
		try 
		{
			String query	= "SELECT `id` FROM users" +
					" WHERE  `username` = '"+ username +"'";
			
			rs = customQuery(query);
			
			if(rs.next()){
				user_id = rs.getInt(1);
			}
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		return user_id;
	}
	
	public int getServiceId(String service) {
		
		int service_id = -1;

		try 
		{
			String query	= "SELECT `id` FROM services" +
					" WHERE  `name` = '"+ service +"'";
			
			rs = customQuery(query);
			
			if(rs.next()){
				service_id = rs.getInt(1);
			}
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		return service_id;
	}

	// Logout:
	public void logout(String username){

		// Set user to inactive
		String query = "UPDATE  users SET  `active` =  '0' "
				+ "WHERE  `username` = '" + username + "'";

		customExecute(query);

		// Remove all services from `user_services` table
		int user_id = getUserId(username);
		String query_delete = "DELETE FROM `user_services` "
				+ "WHERE `user_id` = '" + user_id + "'";

		customExecute(query_delete);

		// Remove all auctions from `auctions` table:
		String query2 = "DELETE FROM `auctions` " + "WHERE `buyer_id` = '"
				+ user_id + "'";

		customExecute(query2);
	} 

	
	// Clear the database for refresh
	public void deleteOnRefresh(String username){

		// Remove all services from `user_services` table
		int user_id = getUserId(username);
		
		String query1 = "DELETE FROM `user_services` "
							+ "WHERE `user_id` = '" + user_id + "'";

		customExecute(query1);

		// Remove all auctions from `auctions` table:
		String query2 = "DELETE FROM `auctions` " + "WHERE `buyer_id` = '"
				+ user_id + "'";

		customExecute(query2);
	} 

	
	public boolean customExecute(String query) {

		boolean rez = false;
		try 
		{			
			db.setCommand(query);
			rez = db.execute();
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		return rez;
	}
	
	public ResultSet customQuery(String query) {
		
		ResultSet rez = null;
		try 
		{
			db.setCommand(query);
			rez = db.executeQuery();
		}
		
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		return rez;
	}	
}



